<!DOCTYPE HTML>
<html lang="zh-CN">
<head>
    

<head><meta name="generator" content="Hexo 3.8.0">
    <meta charset="utf-8">
    <meta name="keywords" content="SQL, 炫杉">
    <meta name="description" content="noSql数据库满天飞的情况下，安下心来让SQL尘埃落定吧。（出来混总要还的😌）《SQL必知必会》读书笔记，Ben Forta著。

了解SQLStructured Query Language 结构化查询语言
基础语法
|   表示 在">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="renderer" content="webkit|ie-stand|ie-comp">
    <meta name="mobile-web-app-capable" content="yes">
    <meta name="format-detection" content="telephone=no">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
    <title>SQL | 炫杉</title>
    <link rel="icon" type="image/png" href="/favicon.png">

    <link rel="stylesheet" type="text/css" href="/libs/awesome/css/font-awesome.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/materialize/css/materialize.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/aos/aos.css">
    <link rel="stylesheet" type="text/css" href="/libs/animate/animate.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/lightGallery/css/lightgallery.min.css">
    <link rel="stylesheet" type="text/css" href="/css/matery.css">
    <link rel="stylesheet" type="text/css" href="/css/my.css">

    <script src="/libs/jquery/jquery-2.2.0.min.js"></script>
<link rel="stylesheet" href="/css/prism-tomorrow.css" type="text/css">
<link rel="stylesheet" href="/css/prism-line-numbers.css" type="text/css"></head>

</head>

<body>

<header class="navbar-fixed">
    <nav id="headNav" class="bg-color nav-transparent">
        <div id="navContainer" class="container">
            <div class="nav-wrapper">
                <div class="brand-logo">
                    <a href="/" class="waves-effect waves-light">
                        
                        <img src="/medias/logo.png" class="logo-img hide-on-small-only">
                        
                        <span class="logo-span">炫杉</span>
                    </a>
                </div>
                <a href="#" data-activates="mobile-nav" class="button-collapse"><i class="fa fa-navicon"></i></a>
<ul class="right">
    
    <li class="hide-on-med-and-down">
        <a href="/" class="waves-effect waves-light">
            
            <i class="fa fa-home"></i>
            
            <span>Index</span>
        </a>
    </li>
    
    <li class="hide-on-med-and-down">
        <a href="/tags" class="waves-effect waves-light">
            
            <i class="fa fa-tags"></i>
            
            <span>Tags</span>
        </a>
    </li>
    
    <li class="hide-on-med-and-down">
        <a href="/categories" class="waves-effect waves-light">
            
            <i class="fa fa-bookmark"></i>
            
            <span>Categories</span>
        </a>
    </li>
    
    <li class="hide-on-med-and-down">
        <a href="/archives" class="waves-effect waves-light">
            
            <i class="fa fa-archive"></i>
            
            <span>Archives</span>
        </a>
    </li>
    
    <li class="hide-on-med-and-down">
        <a href="/about" class="waves-effect waves-light">
            
            <i class="fa fa-user-circle-o"></i>
            
            <span>About</span>
        </a>
    </li>
    
    <li class="hide-on-med-and-down">
        <a href="https://oo1.win" class="waves-effect waves-light">
            
            <i class="fa fa-star"></i>
            
            <span>Bookmark</span>
        </a>
    </li>
    
    <li>
        <a id="toggleSearch" class="waves-effect waves-light">
            <i id="searchIcon" class="mdi-action-search"></i>
        </a>
    </li>

</ul>

<div class="side-nav" id="mobile-nav">

    <div class="mobile-head bg-color">
        
        <img src="/medias/logo.png" class="logo-img circle responsive-img">
        
        <div class="logo-name">炫杉</div>
        <div class="logo-desc">
            
            提着鸟笼的老头站在一旁拍我肩膀 我猜他想的应该和我一样
            
        </div>
    </div>

    <ul class="menu-list mobile-menu-list">
        
        <li>
            <a href="/" class="waves-effect waves-light">
                
                <i class="fa fa-fw fa-home"></i>
                
                Index
            </a>
        </li>
        
        <li>
            <a href="/tags" class="waves-effect waves-light">
                
                <i class="fa fa-fw fa-tags"></i>
                
                Tags
            </a>
        </li>
        
        <li>
            <a href="/categories" class="waves-effect waves-light">
                
                <i class="fa fa-fw fa-bookmark"></i>
                
                Categories
            </a>
        </li>
        
        <li>
            <a href="/archives" class="waves-effect waves-light">
                
                <i class="fa fa-fw fa-archive"></i>
                
                Archives
            </a>
        </li>
        
        <li>
            <a href="/about" class="waves-effect waves-light">
                
                <i class="fa fa-fw fa-user-circle-o"></i>
                
                About
            </a>
        </li>
        
        <li>
            <a href="https://oo1.win" class="waves-effect waves-light">
                
                <i class="fa fa-fw fa-star"></i>
                
                Bookmark
            </a>
        </li>
        
        
    </ul>

    <div class="social-link"><a href="https://github.com/ixsim" class="tooltipped" target="_blank" data-tooltip="访问我的GitHub" data-position="top" data-delay="50">
    <i class="fa fa-github"></i>
</a>

<a href="mailto:chenjiayin1990@163.com" class="tooltipped" target="_blank" data-tooltip="邮件:chinalixs@qq.com" data-position="top" data-delay="50">
    <i class="fa fa-envelope-open"></i>
</a>

<a href="http://wpa.qq.com/msgrd?v=3&uin=6956820&site=qq&menu=yes" class="tooltipped" data-tooltip="QQ: 6956820" data-position="top" data-delay="50">
    <i class="fa fa-qq"></i>
</a>
</div>
</div>

            </div>
        </div>

        
    </nav>
</header>



<div class="bg-cover post-cover" style="background-image: url('/medias/featureimages/6.jpg')">
    <div class="container">
        <div class="row">
            <div class="col s12 m12 l12">
                <div class="brand">
                    <div class="description center-align post-title">
                        SQL
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>



<main class="post-container content">

    
    <link rel="stylesheet" href="/libs/tocbot/tocbot.css">
<style>
    #articleContent h1,
    #articleContent h2,
    #articleContent h3,
    #articleContent h4,
    #articleContent h5,
    #articleContent h6 {
        padding-top: 76px;
        margin-top: -76px;
    }

    #articleContent h1 {
        line-height: 3.5rem;
    }

    #articleContent h2 {
        line-height: 3.2rem;
    }

    #articleContent h3 {
        line-height: 2.8rem;
    }

    #articleContent h4 {
        line-height: 2.5rem;
    }

    #articleContent h5 {
        line-height: 2.2rem;
    }

    #articleContent h6 {
        line-height: 1.9rem;
    }

    #articleContent :focus {
        outline: none;
    }

    .toc-fixed {
        position: fixed;
        top: 64px;
    }

    .toc-widget {
        padding-left: 20px;
    }

    .toc-widget .toc-title {
        margin: 35px 0 15px 0;
        padding-left: 17px;
        font-size: 1.5rem;
        font-weight: bold;
        line-height: 1.5rem;
    }

    .toc-widget ol {
        padding: 0;
        list-style: none;
    }

    #toc-content ol {
        padding-left: 10px;
    }

    #toc-content ol li {
        padding-left: 10px;
    }

    #toc-content .toc-link:hover {
        color: #42b983;
        font-weight: 700;
        text-decoration: underline;
    }

    #toc-content .toc-link::before {
        background-color: transparent;
        max-height: 25px;
    }

    #toc-content .is-active-link {
        color: #42b983;
    }

    #toc-content .is-active-link::before {
        background-color: #42b983;
    }
</style>
<div class="row">
    <div class="col s12 m12 l9">
        <!-- 文章内容详情 -->
<div id="artDetail">
    <div class="card">
        <div class="card-content article-info">
            
            <div class="article-tag">
                
                <a href="/tags/SQL/" target="_blank">
                    <span class="chip bg-color">SQL</span>
                </a>
                
            </div>
            
            <div class="post-info">
                
                <span class="post-cate">
                    <i class="fa fa-bookmark fa-fw icon-category"></i>
                    
                    <a href="/categories/Others/" class="post-category" target="_blank">
                        Others
                    </a>
                    
                </span>
                

                <span class="post-date">
                    <i class="fa fa-clock-o fa-fw"></i>2018-02-16
                </span>
            </div>
        </div>
        <hr>
        <div class="card-content article-card-content">
            <div id="articleContent">
                <p>noSql数据库满天飞的情况下，安下心来让SQL尘埃落定吧。（出来混总要还的😌）<br>《SQL必知必会》读书笔记，Ben Forta著。</p>
<a id="more"></a>
<h1 id="了解SQL"><a href="#了解SQL" class="headerlink" title="了解SQL"></a>了解SQL</h1><p>Structured Query Language 结构化查询语言</p>
<h2 id="基础语法"><a href="#基础语法" class="headerlink" title="基础语法"></a>基础语法</h2><blockquote>
<p>|   表示 在可选项中选择一个</p>
<p>[ ] 表示这个参数可选</p>
</blockquote>
<ul>
<li>ALTER TABLE</li>
</ul>
<p>更新  已存在  表  的  结构</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword">table</span>
<span class="token punctuation">(</span>
    <span class="token keyword">ADD</span><span class="token operator">|</span><span class="token keyword">DROP</span> <span class="token keyword">column</span> datatype <span class="token punctuation">[</span><span class="token boolean">NULL</span><span class="token operator">|</span><span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>CONSTRAINTS<span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token keyword">ADD</span><span class="token operator">|</span><span class="token keyword">DROP</span> <span class="token keyword">column</span> datatype <span class="token punctuation">[</span><span class="token boolean">NULL</span><span class="token operator">|</span><span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>CONSTRAINTS<span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<ul>
<li>COMMIT</li>
</ul>
<p>将 事务 写入 数据库。</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">COMMIT</span> <span class="token punctuation">[</span><span class="token keyword">TRANSACTION</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<ul>
<li>CREATE INDEX</li>
</ul>
<p>在 一个或多个 列 上 创建 索引。</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> i ndexname
<span class="token keyword">ON</span> tablename <span class="token punctuation">(</span><span class="token keyword">column</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<ul>
<li>CREATE PROCEDURE</li>
</ul>
<p>创建存储过程。 Oracle 使用的语法稍有不同。</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> procedurename <span class="token punctuation">[</span>parameters<span class="token punctuation">]</span> <span class="token punctuation">[</span>options<span class="token punctuation">]</span>
<span class="token keyword">AS</span>
SQL statement<span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<ul>
<li>CREATE TABLE </li>
</ul>
<p>创建 新数据库表。</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tablename
<span class="token punctuation">(</span>
    <span class="token keyword">column</span> datatype <span class="token punctuation">[</span><span class="token boolean">NULL</span> INOT <span class="token boolean">NULL</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>CONSTRAINTS<span class="token punctuation">]</span> <span class="token punctuation">,</span>
    <span class="token keyword">column</span> datatype <span class="token punctuation">[</span><span class="token boolean">NULL</span> INOT <span class="token boolean">NULL</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>CONSTRAINTS<span class="token punctuation">]</span> <span class="token punctuation">,</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<ul>
<li>CREATE VIEW</li>
</ul>
<p>用来创建 一个或多个 表上的新视图。</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> viewname <span class="token keyword">AS</span>
<span class="token keyword">SELECT</span> <span class="token keyword">columns</span><span class="token punctuation">,</span> 。。。
<span class="token keyword">FROM</span> <span class="token keyword">tables</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">[</span><span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">HAVING</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<ul>
<li>DELETE</li>
</ul>
<p>从表中 删除 一行或多行。</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> tablename
<span class="token punctuation">[</span><span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<ul>
<li>DROP</li>
</ul>
<p>永久地删除数据厍对象</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> <span class="token operator">|</span> <span class="token keyword">PROCEDURE</span> <span class="token operator">|</span> <span class="token keyword">TABLE</span> <span class="token operator">|</span> <span class="token keyword">VIEW</span>
indexname <span class="token operator">|</span> procedurename <span class="token operator">|</span> tablename <span class="token operator">|</span> viewname<span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<ul>
<li>INSERT</li>
</ul>
<p>为表添加一行。</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> tablename <span class="token punctuation">[</span><span class="token punctuation">(</span>co1umns<span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">]</span>
<span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token keyword">values</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<ul>
<li>INSERT SELECT </li>
</ul>
<p>将SELECT的结果插人到一个表</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> tablename <span class="token punctuation">[</span> <span class="token punctuation">(</span><span class="token keyword">columns</span><span class="token punctuation">,</span><span class="token punctuation">)</span><span class="token punctuation">]</span>
<span class="token keyword">SELECT</span> <span class="token keyword">columns</span> ， <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> tablename <span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">[</span><span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<ul>
<li>ROLLBACK</li>
</ul>
<p>用于撤销-一个事务块。</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">ROLLBACK</span> <span class="token punctuation">[</span><span class="token keyword">TO</span> savepointname<span class="token punctuation">]</span><span class="token punctuation">;</span>
或
<span class="token keyword">ROLLBACK</span> TRANSCATION<span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<ul>
<li>SELECT</li>
</ul>
<p>用于从一个或多个表(视图)中检索数据。</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> columnname ，<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">FROM</span> tablename ， <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">[</span><span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">UNION</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">HAVING</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
<span class="token punctuation">[</span>ORDERBY <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<ul>
<li>UPDATE</li>
</ul>
<p>更新表中的一行或多行。</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">UPDATE</span> tablename
<span class="token keyword">SET</span> columname <span class="token operator">=</span> <span class="token keyword">value</span> <span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">[</span><span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<h2 id="数据类型"><a href="#数据类型" class="headerlink" title="数据类型"></a>数据类型</h2><h3 id="字符串"><a href="#字符串" class="headerlink" title="字符串"></a>字符串</h3><ul>
<li>CHAR<ul>
<li>1-255个字符的定长字符串。长度必须在创建时规定。</li>
</ul>
</li>
<li>NCHAR<ul>
<li>CHAR的特殊形式。用来支持多字节或Unicode字符。</li>
</ul>
</li>
<li>TEXT (也称 LONG\MEMO\VARCHAR)<ul>
<li>变长文本</li>
</ul>
</li>
<li>NVARCHAR<ul>
<li>TEXT的特殊形式。</li>
</ul>
</li>
</ul>
<h3 id="数值"><a href="#数值" class="headerlink" title="数值"></a>数值</h3><ul>
<li>BTT 单个二进制位</li>
<li>DECIMAL 定点、或浮点可变的浮点值</li>
<li>FLOAT 浮点值</li>
<li>INT 4字节整数</li>
<li>REAL 4字节浮点</li>
<li>SMALLINT 2字节整数</li>
<li>TINYINT 1字节，0-255的整数</li>
</ul>
<blockquote>
<p>多数DMBS支持货币数据类型 <code>MONEY/CURRENCY</code> 有特定取值范围的DECIMAL类型。更适合存货币值</p>
</blockquote>
<h3 id="日期时间"><a href="#日期时间" class="headerlink" title="日期时间"></a>日期时间</h3><ul>
<li>DATE 日期</li>
<li>DATETIME 日期时间</li>
<li>SMALLDATETIME 日期时间精确到分</li>
<li>TIME 时间</li>
</ul>
<h1 id="检索数据"><a href="#检索数据" class="headerlink" title="检索数据"></a>检索数据</h1><ul>
<li>SELECT <em> `</em>`选择所有</li>
<li>SELECT DISTINCT name <code>DISTINCT</code> 结果去重</li>
</ul>
<h3 id="限制结果"><a href="#限制结果" class="headerlink" title="限制结果"></a>限制结果</h3><p>返回前5行</p>
<ul>
<li><p>MySQL PostgreSQL SQLite MariaDB<br>  SELECT xxx FROM xxx LIMIT 5;</p>
</li>
<li><p>进阶：LIMIT 后跟两个参数</p>
<pre class="line-numbers language-sql"><code class="language-sql">  <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">,</span><span class="token number">2</span> <span class="token comment" spellcheck="true">-- 从第10个往后输出2个。输出的是 11 和 12</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
</li>
</ul>
<h3 id="注释"><a href="#注释" class="headerlink" title="注释"></a>注释</h3><pre class="line-numbers language-sql"><code class="language-sql"><span class="token comment" spellcheck="true"># 可以这样</span>
<span class="token keyword">SELECT</span> xxx <span class="token comment" spellcheck="true">-- 可以这样</span>
<span class="token comment" spellcheck="true">/*也可以
这样*/</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>
<h1 id="排序检索数据"><a href="#排序检索数据" class="headerlink" title="排序检索数据"></a>排序检索数据</h1><pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> id<span class="token punctuation">,</span>name<span class="token punctuation">,</span>grade
<span class="token keyword">FROM</span> Stu
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> grade<span class="token punctuation">,</span>name<span class="token punctuation">;</span> <span class="token comment" spellcheck="true">--当出现并列分数则按姓名排列</span>
<span class="token comment" spellcheck="true">#也可以写成：</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token number">3</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">;</span> <span class="token comment" spellcheck="true">--3就是grade 2就是name 。对应于SELECT第一句</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p><strong>重要：应保证 ORDER BY 子句是 SELECT 语句中的 <em>最后一个</em> 子句 ，否则会报错！</strong></p>
<h2 id="排序方向："><a href="#排序方向：" class="headerlink" title="排序方向："></a>排序方向：</h2><p>默认升序[A~Z] ，改降序：</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> grade <span class="token keyword">DESC</span>
<span class="token comment" spellcheck="true">#多个属性想降序，必须对每一列都进行声明！</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> grade <span class="token keyword">DESC</span><span class="token punctuation">,</span> name  <span class="token comment" spellcheck="true">--name就是升序(ASC)排列</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<h1 id="过滤数据"><a href="#过滤数据" class="headerlink" title="过滤数据"></a>过滤数据</h1><p>WHERE 子句操作符：</p>
<table>
<thead>
<tr>
<th>= 等于</th>
<th>&gt; 大于</th>
<th>&gt;= 或 !&lt; 大于等于</th>
<th>BEWTEEN AND在指定值之间</th>
</tr>
</thead>
<tbody>
<tr>
<td>&lt;&gt; 或 != 不等于</td>
<td>&lt; &lt;=</td>
<td>&lt;= 或  !&gt; 小于等于</td>
<td>IS NULL 为NULL</td>
</tr>
</tbody>
</table>
<h1 id="高级过滤"><a href="#高级过滤" class="headerlink" title="高级过滤"></a>高级过滤</h1><h2 id="组合WHERE子句"><a href="#组合WHERE子句" class="headerlink" title="组合WHERE子句"></a>组合WHERE子句</h2><p>用AND或OR组合子句</p>
<h2 id="AND和OR的顺序"><a href="#AND和OR的顺序" class="headerlink" title="AND和OR的顺序"></a>AND和OR的顺序</h2><p>AND级别比OR高。 如果需要改变顺序，请加括号：</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">WHERE</span> <span class="token punctuation">(</span>name <span class="token operator">=</span> <span class="token string">'XiaoMing'</span> <span class="token operator">OR</span> name <span class="token operator">=</span> <span class="token string">'XiaoHong'</span><span class="token punctuation">)</span>
    <span class="token operator">AND</span> grade <span class="token operator">=</span> <span class="token number">100</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h2 id="IN操作符"><a href="#IN操作符" class="headerlink" title="IN操作符"></a>IN操作符</h2><pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">WHERE</span> id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token string">'001'</span><span class="token punctuation">,</span><span class="token string">'002'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#等价于OR</span>
<span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token string">'001'</span> <span class="token operator">OR</span> id <span class="token operator">=</span> <span class="token string">'002'</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<p>IN的优点：</p>
<p>简单直观。比OR更快。</p>
<p>可以包含其他SELECT子句后面会介绍。</p>
<h2 id="NOT-操作符"><a href="#NOT-操作符" class="headerlink" title="NOT 操作符"></a>NOT 操作符</h2><p>否定后面的条件。不单独使用。</p>
<pre class="line-numbers language-SQL"><code class="language-SQL">WHERE NOT id = '01'
#等价于
WHERE id != '01'
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<p>那为啥还要用NOT呢？</p>
<p>在复杂的条件中，可以迅速找出 <strong>不符合</strong> 条件的</p>
<h1 id="通配符过滤"><a href="#通配符过滤" class="headerlink" title="通配符过滤"></a>通配符过滤</h1><p>使用谓词 <code>LIKE</code> </p>
<ul>
<li>%通配符</li>
</ul>
<p>表示任意字符出现任意次</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">WHERE</span> name <span class="token operator">LIKE</span> <span class="token string">'Xiao%'</span>
<span class="token comment" spellcheck="true">#XiaoMing XiaoHong XiaoLiang就都被搜出来了</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<p>%可以匹配 0 个 1 个 多个字符（包括空格）。当然 ，并不会匹配NULL值</p>
<ul>
<li>_通配符</li>
</ul>
<p>_只匹配一个字符</p>
<ul>
<li>[]通配符</li>
</ul>
<p>匹配字符集（仅SQL Server支持）从<code>[ ]</code>里取一个字符</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">WHERE</span> name <span class="token operator">LIKE</span> <span class="token string">'Xiao[MH]'</span>  <span class="token comment" spellcheck="true">-- 匹配XiaoM和XiaoH</span>
<span class="token keyword">WHERE</span> name <span class="token operator">LIKE</span> <span class="token string">'Xiao[^MH]'</span> <span class="token comment" spellcheck="true">-- 除了XiaoM和XiaoH所有的</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h2 id="通配符技巧"><a href="#通配符技巧" class="headerlink" title="通配符技巧"></a>通配符技巧</h2><ul>
<li>不要过度使用通配符。有其他操作符能达到目的应优先考虑其他。</li>
<li>通配符置于开始处，搜索起来是最慢的</li>
</ul>
<hr>
<p><strong>SELECT name + ‘(‘ + classId + ‘)’拼接字段</strong></p>
<p>就是合并列</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token comment" spellcheck="true">#在SQL Server中 用 +</span>
<span class="token keyword">SELECT</span> name <span class="token operator">+</span> <span class="token string">'('</span> <span class="token operator">+</span> classId <span class="token operator">+</span> <span class="token string">')'</span>
<span class="token comment" spellcheck="true">#在DB2 Oracle PostgreSQL SQLite中 用 ||</span>
<span class="token keyword">SELECT</span> name <span class="token operator">||</span> <span class="token string">'('</span> <span class="token operator">||</span> classId <span class="token operator">||</span> <span class="token string">')'</span>
<span class="token comment" spellcheck="true">#在 MySQL MariaDB 中</span>
<span class="token keyword">SELECT</span> Concat<span class="token punctuation">(</span>name<span class="token punctuation">,</span> <span class="token string">' ('</span><span class="token punctuation">,</span> classId<span class="token punctuation">,</span> <span class="token string">')'</span><span class="token punctuation">)</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<blockquote>
<p>TRIM 函数 去某个字段的空格</p>
<p>LTRIM() 去掉字符左边的空格  RTRIM()右边   TRIM()两边</p>
</blockquote>
<h2 id="使用别名"><a href="#使用别名" class="headerlink" title="使用别名"></a>使用别名</h2><p>拼接之后 AS newName 给新的字段用别名</p>
<h2 id="算数计算"><a href="#算数计算" class="headerlink" title="算数计算"></a>算数计算</h2><pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> quantity<span class="token punctuation">,</span> price<span class="token punctuation">,</span> quantity<span class="token operator">*</span>price <span class="token keyword">AS</span> Pay
<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<h1 id="函数"><a href="#函数" class="headerlink" title="函数"></a>函数</h1><p>不同的sql还不一样名。不怎么通用。 P65 Mark</p>
<h1 id="汇总数据"><a href="#汇总数据" class="headerlink" title="汇总数据"></a>汇总数据</h1><h2 id="聚集函数"><a href="#聚集函数" class="headerlink" title="聚集函数"></a>聚集函数</h2><ul>
<li><p>AVG() 返回列的平均值</p>
</li>
<li><p>COUNT()返回列的行数</p>
<ul>
<li>COUNT(columnName) 对特定的列计数，忽略NULL值</li>
<li>COUNT( * ) 表中的行数计数。不论是不是NIULL都计数。</li>
</ul>
</li>
<li><p>MAX() MIN()</p>
</li>
<li><p>SUM() 列值之和</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>price<span class="token punctuation">)</span> <span class="token keyword">AS</span> avg_price <span class="token keyword">FROM</span> xxx<span class="token punctuation">;</span> <span class="token comment" spellcheck="true">--返回平均价格</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
</li>
</ul>
<h2 id="DISTINCT去重"><a href="#DISTINCT去重" class="headerlink" title="DISTINCT去重"></a>DISTINCT去重</h2><pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span><span class="token keyword">DISTINCT</span> price<span class="token punctuation">)</span> <span class="token keyword">AS</span> avg_price
<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<h1 id="分组"><a href="#分组" class="headerlink" title="分组"></a>分组</h1><p>GROPU BY和HAVING</p>
<h2 id="不用GROUP-BY"><a href="#不用GROUP-BY" class="headerlink" title="不用GROUP BY"></a>不用GROUP BY</h2><pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>grade<span class="token punctuation">)</span> <span class="token keyword">AS</span> 小明的总分 <span class="token keyword">FROM</span> Stu
<span class="token keyword">WHERE</span> name <span class="token operator">=</span> <span class="token string">'小明'</span>；
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<p>但这时候如果还想看小红，还想看小亮的总分，总不能挨个都写到WHERE里。</p>
<h2 id="GROUP-BY"><a href="#GROUP-BY" class="headerlink" title="GROUP BY"></a>GROUP BY</h2><pre class="line-numbers language-Sql"><code class="language-Sql">SELECT nmae, SUM(grade) AS 总分 FROM Stu
GROUP BY name;
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<ul>
<li>GROUP BY必须出现于WHERE之后 ORDER BY之前</li>
<li>NULL也会被分成一组</li>
</ul>
<h2 id="过滤分组"><a href="#过滤分组" class="headerlink" title="过滤分组"></a>过滤分组</h2><p>WHERE过滤的只是行，看上去像是分组了。</p>
<p>如果想排除分组怎么办？（比如不看小丽的总分）</p>
<p>HAVING，支持所有WHERE操作符。语法相同。</p>
<blockquote>
<p>差别：</p>
<p>WHERE在分组前进行过滤，过滤掉的行不参与分组。</p>
<p>HAVING再对分组后的结果过滤</p>
</blockquote>
<h1 id="子查询"><a href="#子查询" class="headerlink" title="子查询"></a>子查询</h1><h2 id="IN-SELECT"><a href="#IN-SELECT" class="headerlink" title="IN(SELECT)"></a>IN(SELECT)</h2><p>用 IN 操作符 嵌套 SECLECT 查询</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> name <span class="token keyword">FROM</span> Stu
<span class="token keyword">WHERE</span> grade <span class="token operator">IN</span> <span class="token punctuation">(</span>
        <span class="token keyword">SELECT</span> grade <span class="token keyword">FROM</span> Stu
        <span class="token keyword">WHERE</span> grade <span class="token operator">></span> <span class="token number">60</span>
    <span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<ul>
<li>作为子查询的SELECT子句只能是单列</li>
<li>多次嵌套会影响效率</li>
</ul>
<h2 id="作为计算字段"><a href="#作为计算字段" class="headerlink" title="作为计算字段"></a>作为计算字段</h2><p>当做返回值</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> name<span class="token punctuation">,</span> <span class="token punctuation">(</span>
        <span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span>
        <span class="token keyword">FROM</span> tab1
        <span class="token keyword">WHERE</span> tab1<span class="token punctuation">.</span>id <span class="token operator">=</span> tab2<span class="token punctuation">.</span>id
    <span class="token punctuation">)</span> <span class="token keyword">AS</span> quantity
<span class="token keyword">FROM</span> tab2
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<ul>
<li><p>完全限定列名</p>
<p>两个表里都已id字段，要加前缀的</p>
</li>
<li><p>后面学习的JOIN代替上面的方法更有效</p>
</li>
</ul>
<h1 id="联结表"><a href="#联结表" class="headerlink" title="联结表"></a>联结表</h1><h2 id="笛卡尔积"><a href="#笛卡尔积" class="headerlink" title="笛卡尔积"></a>笛卡尔积</h2><p>WHERE条件子句不写，直接</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> id<span class="token punctuation">,</span>name，grade
<span class="token keyword">FROM</span> tab1<span class="token punctuation">,</span>tab2
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h2 id="内联结-等值联结"><a href="#内联结-等值联结" class="headerlink" title="内联结 等值联结"></a>内联结 等值联结</h2><pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> grade
<span class="token keyword">FROM</span> Stu <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> Quit
    <span class="token keyword">ON</span> Stu<span class="token punctuation">.</span>id <span class="token operator">=</span> Quit<span class="token punctuation">.</span>id
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<p>等价于</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> grade
<span class="token keyword">FROM</span> Stu<span class="token punctuation">,</span> Quit
<span class="token keyword">WHERE</span> Stu<span class="token punctuation">.</span>id <span class="token operator">=</span> Quit<span class="token punctuation">.</span>id
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<p>只不过关键词 WHERE 换成 ON</p>
<blockquote>
<p>ANSI SQL规范首选 INNER JOIN 语法</p>
<p>第二种写法是很Low的哦</p>
</blockquote>
<p>但第二种写法还可以联结多个表，当然性能会下降</p>
<h1 id="高级联结"><a href="#高级联结" class="headerlink" title="高级联结"></a>高级联结</h1><h2 id="表的别名"><a href="#表的别名" class="headerlink" title="表的别名"></a>表的别名</h2><p>使用AS ，注意，也可以在FROM之前的SELECT中使用哦</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> A<span class="token punctuation">.</span><span class="token operator">*</span><span class="token punctuation">,</span> B<span class="token punctuation">.</span>name
<span class="token keyword">FROM</span> tab1 <span class="token keyword">AS</span> A<span class="token punctuation">,</span> tab2 <span class="token keyword">AS</span> B
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<hr>
<p>除了内联结（等值联结） 还有三种其他联结</p>
<h2 id="自联结"><a href="#自联结" class="headerlink" title="自联结"></a>自联结</h2><p>假如查询和小明考了一样分数的同学们的名字，用嵌套查询的方式可以这样：</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> name <span class="token keyword">FROM</span> Grade
<span class="token keyword">WHERE</span> grade <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> grade <span class="token keyword">FROM</span> Grade 
              <span class="token keyword">WHERE</span> name <span class="token operator">=</span> <span class="token string">'小明'</span><span class="token punctuation">)</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<p>用自联结就可以这样：</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> name
<span class="token keyword">FROM</span> Grade <span class="token keyword">AS</span> g1<span class="token punctuation">,</span> Grade <span class="token keyword">AS</span> g2
<span class="token keyword">WHERE</span> g2<span class="token punctuation">.</span>name <span class="token operator">=</span> <span class="token string">'小明'</span> 
    <span class="token operator">AND</span> g1<span class="token punctuation">.</span>grade <span class="token operator">=</span> g2<span class="token punctuation">.</span>grade
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>
<blockquote>
<p>一般情况下使用[[联结]]比使用[[子查询]]快得多。</p>
<p>当然也可以试用两种方法，以确定哪种性能更好</p>
</blockquote>
<h2 id="自然联结"><a href="#自然联结" class="headerlink" title="自然联结"></a>自然联结</h2><p>排除多次出现。每一列只返回一次。</p>
<p>一般通过对一个表使用通配符(SELECT *)，对其他表使用 明确的 子集来完成。</p>
<p>比如查询同学的成绩。学号的那张表肯定是唯一的，就可以</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> S<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token punctuation">,</span> xxxxxx
<span class="token keyword">FROM</span> Stu <span class="token keyword">AS</span> S<span class="token punctuation">,</span> tab2<span class="token punctuation">,</span> xxxxx
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h2 id="外联结"><a href="#外联结" class="headerlink" title="外联结"></a>外联结</h2><p>左外联结和右外联结，</p>
<p>有时候 表1 和 表2 联结，并不能出现一一对应的情况，这时候以表一的数据为准，【表一中有，表二中没有的】 联结之后填充NULL值。就是左外联结。 关键词是</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">FROM</span> tab1 <span class="token keyword">LEFT</span> <span class="token keyword">OUTER</span> <span class="token keyword">JOIN</span> tab2
    <span class="token keyword">ON</span> 条件
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<p>右外联结 就是 <code>RIGHT OUTER JOIN</code></p>
<blockquote>
<p><strong>全外联结</strong>  <code>FULL OUTER JOIN</code></p>
<p>带上左右不关联的行全部返回。但是 MySQL  SQLite MariaDB等不支持 FULL OUTER JOIN 的 语法</p>
</blockquote>
<h1 id="组合查询"><a href="#组合查询" class="headerlink" title="组合查询"></a>组合查询</h1><p>用 UNION 联结两条 SELECT</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> name<span class="token punctuation">,</span>id <span class="token keyword">FROM</span> xxx
<span class="token keyword">WHERE</span> id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token string">'001'</span><span class="token punctuation">,</span><span class="token string">'002'</span><span class="token punctuation">,</span><span class="token string">'003'</span><span class="token punctuation">)</span>
<span class="token keyword">UNION</span>
<span class="token keyword">SELECT</span> name<span class="token punctuation">,</span>id <span class="token keyword">FROM</span> xxx
<span class="token keyword">WHERE</span> xxx
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<blockquote>
<p>上面的例子当然可以用 WHERE 配合 OR 来组合条件完成，</p>
<p>但是当遇到复杂的过滤条件， UNION 处理起来更简单。</p>
<p>性能上的差别，最好是测试以确定更优。</p>
</blockquote>
<ul>
<li>Note：列的数据类型必须完全兼容。上面我给的例子是完全相同的两个列，实际上，只要类型兼容即可（即 必须是DBMS可以隐含转换的类型）。</li>
</ul>
<h2 id="重复的行"><a href="#重复的行" class="headerlink" title="重复的行"></a>重复的行</h2><p>UNOIN 默认会去除重复行。（看上去效果和使用了多个WHERE子句一样）</p>
<p>如果想返回所有行。请使用 <code>UNOIN ALL</code> 关键词。实现合并两个集合而不去重。这样可以解决【用WHERE会去重】的问题。</p>
<blockquote>
<p><strong>其他UNION</strong></p>
<p>既然有并集。就还有 差集：<code>EXCEPT</code>    交集：<code>INTERSECT</code></p>
<p>但实际上他们很少使用，因为可以用 联结  来得到结果</p>
</blockquote>
<h1 id="插入数据"><a href="#插入数据" class="headerlink" title="插入数据"></a>插入数据</h1><p>学了这么久，查插删改一直在查，终于到了插了：</p>
<ul>
<li>插入完整的行</li>
<li>插入行的一部分</li>
<li>插入查询的结果</li>
</ul>
<h2 id="插入完整的行"><a href="#插入完整的行" class="headerlink" title="插入完整的行"></a>插入完整的行</h2><pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> tab1
<span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'001'</span><span class="token punctuation">,</span><span class="token string">'XiaoMing'</span><span class="token punctuation">,</span><span class="token number">99</span><span class="token punctuation">,</span><span class="token boolean">Null</span><span class="token punctuation">,</span><span class="token boolean">Null</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<p>但是这种方法不安全。完全依赖列的顺序插入。表的结构变动后，迟早会出问题。更安全（麻烦）的方法：</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> tab1<span class="token punctuation">(</span>id<span class="token punctuation">,</span>name<span class="token punctuation">,</span>math<span class="token punctuation">,</span>english<span class="token punctuation">,</span>PE<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'001'</span><span class="token punctuation">,</span><span class="token string">'XiaoMing'</span><span class="token punctuation">,</span><span class="token number">99</span><span class="token punctuation">,</span><span class="token boolean">Null</span><span class="token punctuation">,</span><span class="token boolean">Null</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h2 id="插入部分行"><a href="#插入部分行" class="headerlink" title="插入部分行"></a>插入部分行</h2><p>就是说列名可以不写全。但必须满足以下某个条件：</p>
<ul>
<li>该列定义 允许 NULL值</li>
<li>表定义中给出了默认值</li>
</ul>
<h2 id="插入检索的数据"><a href="#插入检索的数据" class="headerlink" title="插入检索的数据"></a>插入检索的数据</h2><pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> tab1<span class="token punctuation">(</span>xxx<span class="token punctuation">,</span>xxx<span class="token punctuation">,</span>xxx<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> xxx<span class="token punctuation">,</span>xxx<span class="token punctuation">,</span>xxx <span class="token keyword">FROM</span> xxx
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<p>其中INSERT 和 SELECT 的列名不要求匹配。插入只看位置。</p>
<h2 id="从一张表复制到另一张表"><a href="#从一张表复制到另一张表" class="headerlink" title="从一张表复制到另一张表"></a>从一张表复制到另一张表</h2><pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">INTO</span> tabCopy <span class="token comment" spellcheck="true">-- 创建一张新表</span>
<span class="token keyword">FROM</span> tab1
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<p>如果只是复制部分列，把 * 换成 列名</p>
<p><strong>MySQL  Oracle  PostgreSQL  SQLite</strong>语法不同：</p>
<pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tabCopy <span class="token keyword">AS</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tab1<span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h1 id="删改"><a href="#删改" class="headerlink" title="删改"></a>删改</h1><h2 id="更新数据"><a href="#更新数据" class="headerlink" title="更新数据"></a>更新数据</h2><pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">UPDATE</span> Stu
<span class="token keyword">SET</span> QQ <span class="token operator">=</span> <span class="token string">'123'</span><span class="token punctuation">,</span> WeChat <span class="token operator">=</span> <span class="token string">'123'</span>
<span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token string">'001'</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<p>更新总是要以  要更新的 表名 开始。</p>
<p>会以 WHERE 子句结束，它告诉DBMS要更新哪一行。</p>
<p><strong>没有WHERE子句会更新所有行</strong> 这不是我们所希望的</p>
<h2 id="删除数据"><a href="#删除数据" class="headerlink" title="删除数据"></a>删除数据</h2><pre class="line-numbers language-sql"><code class="language-sql"><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> Stu
<span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token string">'001'</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<p>DELETE删除的是【行】。一定要带上WHERE，不然就删了所有的行。</p>
<blockquote>
<p>如果想删除所有行，请使用 TRUNCATE TABLE ,它完成相同的工作速度 更快。</p>
</blockquote>

            </div>
            <hr>

            

            <link rel="stylesheet" type="text/css" href="/libs/share/css/share.min.css">

<div id="article-share">
    
    <div class="social-share" data-disabled="qzone" data-wechat-qrcode-helper="<p>微信里点“发现”->“扫一扫”二维码便可查看分享。</p>"></div>
    
</div>

<script src="/libs/share/js/social-share.min.js"></script>

            <div class="reprint">
                <p>
                    <span class="reprint-tip">转载请注明: </span>
                    <a href="https://xuanshan.gitee.io" class="b-link-green">炫杉</a>
                    <i class="fa fa-angle-right fa-lg fa-fw text-color"></i>
                    <a href="/Others/SQL/" class="b-link-green">SQL</a>
                </p>
            </div>
        </div>
    </div>

    

    

    

    
    <div class="livere-card card" data-aos="fade-up">
    <!-- 来必力City版安装代码 -->
    <div id="lv-container" class="card-content" data-id="city" data-uid="MTAyMC8yOTg2My82NDI5">
        <script type="text/javascript">
            (function (d, s) {
                let j, e = d.getElementsByTagName(s)[0];
                if (typeof LivereTower === 'function') {
                    return;
                }

                j = d.createElement(s);
                j.src = 'https://cdn-city.livere.com/js/embed.dist.js';
                j.async = true;

                e.parentNode.insertBefore(j, e);
            })(document, 'script');
        </script>
        <noscript>为正常使用来必力评论功能请激活JavaScript。</noscript>
    </div>
    <!-- City版安装代码已完成 -->
</div>
    

    

<article id="prenext-posts" class="prev-next articles">
    <div class="row article-row">
        
        <div class="article col s12 m6" data-aos="fade-up">
            <div class="article-badge left-badge text-color">上一篇</div>
            <div class="card">
                <a href="/Website/Hexo01/">
                    <div class="card-image">
                        
                        
                        <img src="/medias/featureimages/3.jpg" class="responsive-img" alt="Hexo建站问题总结">
                        
                        <span class="card-title">Hexo建站问题总结</span>
                    </div>
                </a>
                <div class="card-content article-content">
                    <div class="summary">昨天收到一封域名过期的邮件，顺手上去看了看有没有合适的域名。顺手也就搭了个Jekyll的博客。
本托管在coding上，不过云里雾里，弄得还是不满意。
今天决定换成Hexo。再托管在Git上。一节课的功夫，很顺利的就完成了。写个文章记录下。</div>
                    <div class="publish-info">
                        <span class="publish-date">
                            <i class="fa fa-clock-o fa-fw icon-date"></i>2018-03-21
                        </span>
                        <span class="publish-author">
                            
                            <i class="fa fa-bookmark fa-fw icon-category"></i>
                            
                            <a href="/categories/Website/" class="post-category" target="_blank">
                                    Website
                                </a>
                            
                            
                        </span>
                    </div>
                </div>
                
                <div class="card-action article-tags">
                    
                    <a href="/tags/Hexo/" target="_blank">
                        <span class="chip bg-color">Hexo</span>
                    </a>
                    
                </div>
                
            </div>
        </div>
        
        
        <div class="article col s12 m6" data-aos="fade-up">
            <div class="article-badge right-badge text-color">下一篇</div>
            <div class="card">
                <a href="/iOS/UIViewHelper.swift/">
                    <div class="card-image">
                        
                        
                        <img src="/medias/featureimages/2.jpg" class="responsive-img" alt="iOS 圆角、阴影通用配置">
                        
                        <span class="card-title">iOS 圆角、阴影通用配置</span>
                    </div>
                </a>
                <div class="card-content article-content">
                    <div class="summary">这是在小波老师的课上学到的一劳永逸地给控件添加圆角、阴影效果的代码。保存备用。
import UIKit
//圆角
extension UIView {

  @IBInspectable //让它可见
  var cornerReadiu</div>
                    <div class="publish-info">
                            <span class="publish-date">
                                <i class="fa fa-clock-o fa-fw icon-date"></i>2018-01-09
                            </span>
                        <span class="publish-author">
                            
                            <i class="fa fa-bookmark fa-fw icon-category"></i>
                            
                            <a href="/categories/iOS/" class="post-category" target="_blank">
                                    iOS
                                </a>
                            
                            
                        </span>
                    </div>
                </div>
                
                <div class="card-action article-tags">
                    
                    <a href="/tags/Note/" target="_blank">
                        <span class="chip bg-color">Note</span>
                    </a>
                    
                    <a href="/tags/Swift/" target="_blank">
                        <span class="chip bg-color">Swift</span>
                    </a>
                    
                </div>
                
            </div>
        </div>
        
    </div>
</article>
</div>
    </div>
    <div class="col l3 hide-on-med-and-down">
        <div class="toc-widget">
            <div class="toc-title">目录</div>
            <div id="toc-content">

            </div>
        </div>
    </div>
</div>

<script src="/libs/tocbot/tocbot.min.js"></script>
<script>
    $(function () {
        tocbot.init({
            tocSelector: '#toc-content',
            contentSelector: '#articleContent',
            headingSelector: 'h1,h2, h3, h4'
        });

        // modify the toc link href to support Chinese.
        let i = 0;
        let tocHeading = 'toc-heading-';
        $('#toc-content a').each(function () {
            $(this).attr('href', '#' + tocHeading + (++i));
        });

        // modify the heading title id to support Chinese.
        i = 0;
        $('#articleContent').children('h1,h2, h3, h4').each(function () {
            $(this).attr('id', tocHeading + (++i));
        });

        // Set scroll toc fixed.
        let tocHeight = parseInt($(window).height() * 0.4 - 64);
        let $tocWidget = $('.toc-widget');
        $(window).scroll(function () {
            let scroll = $(window).scrollTop();
            /* add post toc fixed. */
            if (scroll > tocHeight) {
                $tocWidget.addClass('toc-fixed');
            } else {
                $tocWidget.removeClass('toc-fixed');
            }
        });
    });
</script>
    

</main>

<footer class="page-footer bg-color">
    <div class="container row center-align">
        <div class="col s12 m8 l8 copy-right">
            &copy;<a href="https://o--o.win" target="_blank">炫杉2018</a> 
            <a href="https://hexo.io/" target="_blank">Hexo</a> 强力驱动
              主题 <a href="https://github.com/blinkfox/hexo-theme-matery" target="_blank">matery</a>        </div>
        <div class="col s12 m4 l4 social-link"><a href="https://github.com/ixsim" class="tooltipped" target="_blank" data-tooltip="访问我的GitHub" data-position="top" data-delay="50">
    <i class="fa fa-github"></i>
</a>

<a href="mailto:chenjiayin1990@163.com" class="tooltipped" target="_blank" data-tooltip="邮件:chinalixs@qq.com" data-position="top" data-delay="50">
    <i class="fa fa-envelope-open"></i>
</a>

<a href="http://wpa.qq.com/msgrd?v=3&uin=6956820&site=qq&menu=yes" class="tooltipped" data-tooltip="QQ: 6956820" data-position="top" data-delay="50">
    <i class="fa fa-qq"></i>
</a>
</div>
    </div>
</footer>

<div class="progress-bar"></div>


<!-- 搜索遮罩框 -->
<div id="searchModal" class="modal">
    <div class="modal-content">
        <div class="search-header">
            <span class="title">搜索</span>
            <input type="search" id="searchInput" name="s" placeholder="请输入搜索的关键字" class="search-input" autofocus>
        </div>
        <div id="searchResult"></div>
    </div>
</div>

<script src="/js/search.js"></script>
<script type="text/javascript">
    searchFunc("/" + "search.xml", 'searchInput', 'searchResult');
</script>
<!-- 回到顶部按钮 -->
<div id="backTop" class="top-scroll">
    <a class="btn-floating btn-large waves-effect waves-light" href="#!">
        <i class="fa fa-angle-up"></i>
    </a>
</div>


<script src="/libs/materialize/js/materialize.min.js"></script>
<script src="/libs/masonry/masonry.pkgd.min.js"></script>
<script src="/libs/aos/aos.js"></script>
<script src="/libs/scrollprogress/scrollProgress.min.js"></script>
<script src="/libs/lightGallery/js/lightgallery-all.min.js"></script>
<script src="/js/matery.js"></script>
</body>
</html>